﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Visio.ForwardEngineer.DataModel
{
    /// <summary>
    /// Holds the structure of a table
    /// </summary>
    public class TableStructure
    {
        public string Name;                     // Name of the table
        public string Schema;                   // Schema of the table
        public ColumnList Columns;              // List of the columns in the table
        public ForeignKeyList ForeignKeys;      // List of the foreign keys defined for the table
        public IndexList Indexes;               // List of the indexes of the table

        /// <summary>
        /// Returns the fully qualified name in the form Schema.TableName and, 
        /// when set, splits the qualified name into schema and table name
        /// </summary>
        public string QualifiedName
        {
            get
            {
                return Schema + '.' + Name;
            }
            set
            {
                if (value.Contains("."))
                {
                    int positionOfDot = value.IndexOf(".");
                    Schema = value.Substring(0, positionOfDot);
                    Name = value.Substring(positionOfDot + 1, value.Length - positionOfDot - 1);
                }
                else
                {
                    Schema = "dbo";
                    Name = value;
                }
            }
        }

        /// <summary>
        /// Standard constructor for a TableStructure
        /// </summary>
        public TableStructure()
        {
            Columns = new ColumnList();
            ForeignKeys = new ForeignKeyList();
            Indexes = new IndexList();
        }

        /// <summary>
        /// Scripts the table as a CREATE Statement
        /// </summary>
        /// <returns></returns>
        public string ScriptCreateTable()
        {
            StringBuilder result = new StringBuilder();
            result.AppendLine(String.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'U'))", Schema, Name));
            result.AppendLine(String.Format("    DROP TABLE [{0}].[{1}]", Schema, Name));
            result.AppendLine(String.Format("GO"));

            //  Let us avoid to generate CREATE SCHEMA for the dbo one. :)
            if (Schema.ToLower() != "dbo")
            {
                result.AppendLine(String.Format("IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{0}')", Schema));
                result.AppendLine(String.Format("    EXEC sp_executeSql N'CREATE SCHEMA {0}'", Schema));
                result.AppendLine(String.Format("GO"));
            }

            result.AppendLine(String.Format("CREATE TABLE [{0}].[{1}] (", Schema, Name));
            List<ColumnStructure> columns = Columns.Values.ToList();
            for (int i = 0; i < columns.Count; i++)
            {
                ColumnStructure column = columns[i];
                string dataType = column.DataType.Replace(';', ',');
                string nullability = (column.isNullable ? " NULL " : " NOT NULL ");
                result.AppendLine(String.Format("[{0}] {1} {2} ", column.Name, dataType, nullability));
                if (i < columns.Count - 1)
                {
                    result.Append(", ");
                }
            }
            result.AppendLine(")");
            result.AppendLine("GO");

            return result.ToString();
        }

        /// <summary>
        /// Scripts the creation of indexes for the table
        /// </summary>
        /// <returns></returns>
        public string ScriptCreateIndexes()
        {
            StringBuilder result = new StringBuilder();

            foreach (IndexStructure index in Indexes.Values)
            {
                if (index.isPrimaryKey)
                {
                    string clusteredClause = index.isClustered ? "CLUSTERED" : "";
                    result.AppendLine(String.Format("ALTER TABLE [{0}].[{1}] ADD CONSTRAINT [{2}] PRIMARY KEY {3} (", Schema, Name, index.Name, clusteredClause));
                    List<ColumnStructure> columnList = index.Columns.Values.ToList();
                    for (int i = 0; i < columnList.Count; i++)
                    {
                        ColumnStructure indexedColumn = columnList[i];
                        result.AppendLine(String.Format("[{0}]", indexedColumn.Name));
                        if (i < (columnList.Count - 1))
                        {
                            result.Append(", ");
                        }
                    }
                    result.AppendLine(")");
                }
            }
            result.AppendLine("GO");

            foreach (IndexStructure index in Indexes.Values)
            {
                if (!index.isPrimaryKey)
                {
                    string uniqueClause = index.isUnique ? "UNIQUE" : "";
                    string clusteredClause = index.isClustered ? "CLUSTERED" : "";
                    result.AppendLine(String.Format("CREATE {4} {0} INDEX [{1}] ON [{2}].[{3}] (", clusteredClause, index.Name, Schema, Name, uniqueClause));
                    List<ColumnStructure> columnList = index.Columns.Values.ToList();
                    for (int i = 0; i < columnList.Count; i++)
                    {
                        ColumnStructure indexedColumn = columnList[i];
                        result.AppendLine(String.Format("[{0}]", indexedColumn.Name));
                        if (i < (columnList.Count - 1))
                        {
                            result.Append(", ");
                        }
                    }
                    result.AppendLine(")");
                }
            }
            result.AppendLine("GO");

            return result.ToString();
        }

        /// <summary>
        /// Scripts the creation of foreign keys for the table
        /// </summary>
        /// <returns></returns>
        public string ScriptCreateForeignKeys()
        {
            StringBuilder result = new StringBuilder();

            foreach (ForeignKeyStructure foreignKey in ForeignKeys.Values)
            {
                result.AppendLine(String.Format("ALTER TABLE [{0}].[{1}] WITH CHECK ADD CONSTRAINT [{2}] FOREIGN KEY (", Schema, Name, foreignKey.Name));
                List<ColumnStructure> sourceColumnList = foreignKey.SourceColumns.Values.ToList();

                for (int i = 0; i < sourceColumnList.Count; i++)
                {
                    ColumnStructure sourceColumn = sourceColumnList[i];
                    result.AppendLine(String.Format("[{0}]", sourceColumn.Name));
                    if (i < (sourceColumnList.Count - 1))
                    {
                        result.Append(", ");
                    }
                }

                result.AppendLine(")");

                result.AppendLine(String.Format("REFERENCES [{0}].[{1}] (", foreignKey.ReferencedTable.Schema, foreignKey.ReferencedTable.Name));
                List<ColumnStructure> referencedColumnList = foreignKey.ReferencedColumns.Values.ToList();

                for (int i = 0; i < referencedColumnList.Count; i++)
                {
                    ColumnStructure referencedColumn = referencedColumnList[i];
                    result.AppendLine(String.Format("[{0}]", referencedColumn.Name));
                    if (i < (referencedColumnList.Count - 1))
                    {
                        result.Append(", ");
                    }
                }

                result.AppendLine(")");

                if (foreignKey.UpdateAction != ForeignKeyStructure.ForeignKeyAction.NoAction)
                {
                    switch (foreignKey.UpdateAction)
                    {
                        case ForeignKeyStructure.ForeignKeyAction.Cascade:
                            result.AppendLine("ON UPDATE CASCADE");
                            break;
                        case ForeignKeyStructure.ForeignKeyAction.SetDefault:
                            result.AppendLine("ON UPDATE SET DEFAULT");
                            break;
                        case ForeignKeyStructure.ForeignKeyAction.SetNull:
                            result.AppendLine("ON UPDATE SET NULL");
                            break;
                    }
                }

                if (foreignKey.DeleteAction != ForeignKeyStructure.ForeignKeyAction.NoAction)
                {
                    switch (foreignKey.DeleteAction)
                    {
                        case ForeignKeyStructure.ForeignKeyAction.Cascade:
                            result.AppendLine("ON DELETE CASCADE");
                            break;
                        case ForeignKeyStructure.ForeignKeyAction.SetDefault:
                            result.AppendLine("ON DELETE SET DEFAULT");
                            break;
                        case ForeignKeyStructure.ForeignKeyAction.SetNull:
                            result.AppendLine("ON DELETE SET NULL");
                            break;
                    }
                }
            }

            result.AppendLine("GO");

            return result.ToString();
        }
    }

    /// <summary>
    /// This class represents a list of tables
    /// </summary>
    public class TableList : Dictionary<string, TableStructure>
    {

        /// <summary>
        /// Adds a new table, indexing by name
        /// </summary>
        /// <param name="newTable"></param>
        public void Add(TableStructure newTable)
        {
            this.Add(newTable.QualifiedName, newTable);
        }

        /// <summary>
        /// When asked for a table name, it receives the qualified name
        /// but, if "dbo." is not specified, it is added here in order
        /// to make the dictionary searchable by
        /// Tables["Test"] and Tables["dbo.Test"] in the same way
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        new public TableStructure this[string index]
        {
            get
            {
                if (!index.Contains("."))
                {
                    index = "dbo." + index;
                }
                return base[index];
            }
            set
            {
                base[index] = value;
            }
        }
    }
}
